-- @owner: @zou_jialiang050
-- @date: 2022/02/24
-- @testpoint: 在向72_2表中插入数据之后，触发器a<2时向72_1中插入a*10,'dd',11，b<3时插入b,'ss',12，a=4时退出触发器

--step1:建表;expect:成功
drop table if exists t_ustore_trigger_case0072_1;
create table t_ustore_trigger_case0072_1 (id int,name varchar(50),sal int) with (storage_type=ustore);
insert into t_ustore_trigger_case0072_1 values(10,'xg',5000);
insert into t_ustore_trigger_case0072_1 values(20,'xig',8000);
insert into t_ustore_trigger_case0072_1 values(30,'xia',5800);
insert into t_ustore_trigger_case0072_1 values(40,'xing',6600);
insert into t_ustore_trigger_case0072_1 values(50,'xiwsg',7000);
drop table if exists t_ustore_trigger_case0072_2;
create table t_ustore_trigger_case0072_2 (id int,name varchar(50),age int) with (storage_type=ustore);
insert into t_ustore_trigger_case0072_2 values(1,'xi',20);
insert into t_ustore_trigger_case0072_2 values(2,'xw',22);
insert into t_ustore_trigger_case0072_2 values(3,'xh',24);
insert into t_ustore_trigger_case0072_2 values(4,'xz',26);

--step2:在向72_2表中插入数据之后，触发器a<2时向72_1中插入a*10,'dd',11，b<3时插入b,'ss',12，a=4时退出触发器;expect:成功
create or replace function f_ustore_trigger_case0072()
returns trigger as $$
declare
a int;
b int;
begin
a :=0;
b :=0;
goto aaaa;
<<aaaa>>
loop

   b:=b+1;
while a <2
  loop
        a:=a+1;
        insert into t_ustore_trigger_case0072_1 values(a*10,'dd',11);
  end loop;
     if b<3 then
	     insert into t_ustore_trigger_case0072_1 values(b,'ss',12);
		 a:=a+1;
     else
   end if;
exit when a=4;
 end loop;
return new;
end;
$$language plpgsql;
/
create trigger tri_ustore_trigger_case0072 after insert on t_ustore_trigger_case0072_2 for each row execute procedure f_ustore_trigger_case0072();
/
insert into t_ustore_trigger_case0072_2 values(7,'tt',789);
select * from t_ustore_trigger_case0072_1;
select * from t_ustore_trigger_case0072_2;

--step3:清理环境;expect:成功
drop trigger if exists tri_ustore_trigger_case0072 on t_ustore_trigger_case0072_2;
drop table if exists t_ustore_trigger_case0072_1 cascade;
drop table if exists t_ustore_trigger_case0072_2 cascade;
drop function if exists f_ustore_trigger_case0072;